/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import AirLine.Person;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;

/**
 *
 * @author Fredrik
 */
public class Persons extends DBObject {
    /*
     * ORACLE_033
     */
    private static final String ORACLE_033_NAME_COLUMN = "NAME";
    private static final String ORACLE_033_SSN_COLUMN = "SSN";
    private static final String ORACLE_033_PERSON_TABLE_NAME = "PASSANGER";
    Hashtable<String, Person> persons_in_ORACLE_033 = new Hashtable<String, Person>();
    
    /*
     * ORACLE_013
     */
    private static final String ORACLE_013_NAME_COLUMN = "NAME";
    private static final String ORACLE_013_SSN_COLUMN = "passenger_id";
    private static final String ORACLE_013_PERSON_TABLE_NAME = "PASSENGER";
    Hashtable<String, Person> persons_in_ORACLE_013 = new Hashtable<String, Person>();
    
    /*
     * MYSQL_013
     */
    private static final String MYSQL_013_NAME_COLUMN = "name";
    private static final String MYSQL_013_SSN_COLUMN = "SSN";
    private static final String MYSQL_013_PERSON_TABLE_NAME = "Passenger";
    Hashtable<String, Person> persons_in_MYSQL_013 = new Hashtable<String, Person>();
    
    public void update(String database) throws SQLException {
        connect();
        if (database.equals(ORACLE_033)) {
            PreparedStatement pstmnt = getConnection(database).prepareStatement(
                    SELECT + 
                    ORACLE_033_NAME_COLUMN + SEPARATOR + 
                    ORACLE_033_SSN_COLUMN + 
                    FROM + 
                    ORACLE_033_PERSON_TABLE_NAME);
            ResultSet result = pstmnt.executeQuery();
            Person p;
            String name;
            String ssn;
            while (result.next()) {
                name = result.getString(ORACLE_033_NAME_COLUMN);
                ssn = result.getString(ORACLE_033_SSN_COLUMN);
                p = new Person(name, ssn);
                persons_in_ORACLE_033.put(ssn, p);
            }
            result.close();
            pstmnt.close();
        }
        else if (database.equals(ORACLE_013)) {
            PreparedStatement pstmnt = getConnection(database).prepareStatement(
                    SELECT + 
                    ORACLE_013_NAME_COLUMN + SEPARATOR + 
                    ORACLE_013_SSN_COLUMN + 
                    FROM + 
                    ORACLE_013_PERSON_TABLE_NAME);
            ResultSet result = pstmnt.executeQuery();
            Person p;
            String name;
            String ssn;
            while (result.next()) {
                name = result.getString(ORACLE_013_NAME_COLUMN);
                ssn = result.getString(ORACLE_013_SSN_COLUMN);
                p = new Person(name, ssn);
                persons_in_ORACLE_013.put(ssn, p);
            }
            result.close();
            pstmnt.close();           
        }
         else if (database.equals(MYSQL_013)) {
            PreparedStatement pstmnt = getConnection(database).prepareStatement(
                    SELECT + 
                    MYSQL_013_NAME_COLUMN + SEPARATOR + 
                    MYSQL_013_SSN_COLUMN + 
                    FROM + 
                    MYSQL_013_PERSON_TABLE_NAME);
            ResultSet result = pstmnt.executeQuery();
            Person p;
            String name;
            String ssn;
            while (result.next()) {
                name = result.getString(MYSQL_013_NAME_COLUMN);
                ssn = result.getString(MYSQL_013_SSN_COLUMN);
                p = new Person(name, ssn);
                persons_in_MYSQL_013.put(ssn, p);
            }
            result.close();
            pstmnt.close();           
        }
        
       closeConnections(); 
    }

    public void registerPerson(Person p, String database) throws SQLException, UnableToBookException {
        
        if (personExist(p, database)) {
            return;
        }else{
            update(database);
            if(personExist(p, database)){
                return;
            }
        }

        connect();
        if (database.equals(ORACLE_033)) {
            
                PreparedStatement pstmnt = getConnection(database).prepareStatement(
                        INSERT_INTO + 
                        ORACLE_033_PERSON_TABLE_NAME + 
                        " (" +
                        ORACLE_033_NAME_COLUMN + SEPARATOR +
                        ORACLE_033_SSN_COLUMN +
                        ") VALUES(?,?)");
                pstmnt.setString(1, p.getName());
                pstmnt.setString(2, p.getSSN());
                pstmnt.execute();
                
            
        }
        else if (database.equals(ORACLE_013)) {
            
                PreparedStatement pstmnt = getConnection(database).prepareStatement(
                        INSERT_INTO + 
                        ORACLE_013_PERSON_TABLE_NAME + 
                        " (" + 
                        ORACLE_013_NAME_COLUMN + SEPARATOR + 
                        ORACLE_013_SSN_COLUMN +
                        ") VALUES(?,?)");
                pstmnt.setString(1, p.getName());
                pstmnt.setString(2, p.getSSN());
                pstmnt.execute();
                
            
        }
        else if (database.equals(MYSQL_013)) {
            
                PreparedStatement pstmnt = getConnection(database).prepareStatement(
                        INSERT_INTO + 
                        MYSQL_013_PERSON_TABLE_NAME + 
                        " (" + 
                        MYSQL_013_NAME_COLUMN + SEPARATOR + 
                        MYSQL_013_SSN_COLUMN + 
                        ") VALUES(?,?)");
                pstmnt.setString(1, p.getName());
                pstmnt.setString(2, p.getSSN());
                pstmnt.execute();
 
        }
        closeConnections();

    }

    private boolean personExist(Person p, String database) {
        if (database.equals(ORACLE_033)) {
            return persons_in_ORACLE_033.containsKey(p.getSSN());
        } 
        else if (database.equals(ORACLE_013)) {
            return persons_in_ORACLE_013.containsKey(p.getSSN());
        } 
        else if (database.equals(MYSQL_013)) {
            return persons_in_MYSQL_013.containsKey(p.getSSN());
        } 
        else {
            return true;
        }
    }
}
